/**
 * output package name
 */
package com.kingdee.eas.custom.jiu.statement;

import icepdf.el;

import java.awt.event.ActionEvent;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.kingdee.bos.ctrl.kdf.table.IRow;
import com.kingdee.bos.ctrl.kdf.table.KDTSortManager;
import com.kingdee.bos.ctrl.kdf.table.KDTableHelper;
import com.kingdee.bos.dao.query.SQLExecutorFactory;
import com.kingdee.bos.ui.face.CoreUIObject;
import com.kingdee.eas.base.commonquery.client.CommonQueryDialog;
import com.kingdee.eas.basedata.assistant.ProjectInfo;
import com.kingdee.eas.basedata.org.CtrlUnitInfo;
import com.kingdee.eas.common.client.SysContext;
import com.kingdee.eas.custom.cost.OnCampus;
import com.kingdee.eas.framework.ICoreBase;
import com.kingdee.jdbc.rowset.IRowSet;

/**
 * output class name
 */
public class BudgetDetailsRptUI extends AbstractBudgetDetailsRptUI
{
    private static final Logger logger = CoreUIObject.getLogger(BudgetDetailsRptUI.class);
    
    public CommonQueryDialog dialog = null;	
    public BudgetDetailsFilterUI userPanel = null;

    // 在生成的dialog类中继承方法 getFilterInfo()，并实现用户想要的过滤条件，应用框架会在取数时调用dialog类自动获取过滤条件  	
    protected CommonQueryDialog initCommonQueryDialog() {   	
	    dialog = super.initCommonQueryDialog();	  
	    try {   			
		    dialog.addUserPanel(getUserPanel());    			
		    dialog.setShowFilter(false);   			
		    dialog.setShowSorter(false);  		 
		    
		    dialog.setHeight(380);   			
		    dialog.setWidth(500);   			
		    dialog.setTitle("客户自定义过滤框");
		    


		    
	    } catch (Exception e) {  			
	    	handUIException(e);    		
	    }    		
	    return dialog;  	
   }


    protected BudgetDetailsFilterUI getUserPanel() throws Exception {
    		
	    if (this.userPanel == null)	                                       
	     // 绑定新建的自定义查询UI	    			
	    this.userPanel = new BudgetDetailsFilterUI();	    		
	    userPanel.onLoad();	    		
	    return this.userPanel;
    	
    }
    
    //首先调出过滤框
    protected boolean initDefaultFilter() {
		return true ;
	}
    
    

    
    String fff ="";
    String ffff ="";
    String dat1 = null;   
    String dat2 = null;     
    private void selectquery() throws ParseException {
    	
	       Object[] s= (Object[]) userPanel.project.getData();
	       
	       String date1 = userPanel.kDDatePicker1.getValue().toString();
	       String date2 = userPanel.kDDatePicker2.getValue().toString();
	     
	       SimpleDateFormat sfEnd = new SimpleDateFormat("yyyy-MM-dd");
	       SimpleDateFormat sfStart = new SimpleDateFormat("EEE MMM dd HH:mm:ss zzz yyyy",java.util.Locale.ENGLISH) ;
	       dat1 = sfEnd.format(sfStart.parse(date1));
	       dat2 = sfEnd.format(sfStart.parse(date2));
	       

	    
	     
    	 if (s==null || s[0]==null ) {
    		 fff ="";
    		 ffff="";
		}else {
			fff = " and p.fid in (";
			ffff=" and p.fid in (";
			for(Object s1:s){
				ProjectInfo f = (ProjectInfo)s1;    		
	    		 String ff = f.getId().toString(); 		 
	    		 fff = fff +"'"+ ff+"',"; 
	    		 ffff=ffff +"'"+ ff+"',"; 
	    	 }
	    	 fff = fff.substring(0, fff.length()-1)+")";
	    	 ffff=ffff.substring(0, ffff.length()-1)+")";
		}
	}

    public void onLoad() throws Exception {
    	super.onLoad();
    	selectquery();
    	
    	setValueToTable();
    	
    	this.tblMain.setColumnMoveable(true);
		KDTableHelper.setEnterKeyJumpOrientation(tblMain, KDTableHelper.HORIZON);
		for (int i = 0; i < tblMain.getColumnCount(); i++) {
			tblMain.getColumn(i).setSortable(true);
		}
		KDTSortManager sm = new KDTSortManager(tblMain);
		sm.setSortAuto(true);
    }
    

	private void setValueToTable() throws Exception {
		CtrlUnitInfo  company= SysContext.getSysContext().getCurrentCtrlUnit();//获取当前管理单元CU ,比财务组织'成本中心更上一级  	
		String companyId = company.getId().toString();
//    	String x = "4/wAAAABeU3M567U";
		
    	//表名
    	String sql1="/*dialect*/ select bgScheme.FRefCube a from  T_MBG_BgScheme bgScheme  where bgScheme.FOrgUnitID='"+companyId+"' and FIsFormal = '1'"; 	
    	IRowSet ra = SQLExecutorFactory.getRemoteInstance(sql1).executeSQL();
        ra.next();
        String a = ra.getString("a");
   	
    	String da = ("" +new Date()).substring(("" +new Date()).length()-4);
    	
    	//sql Server数据库
    	String sql_sqlServer="/*dialect*/  select baseUnit.FName_L2 as budgetTissue, I.FName_l2 as budgetCourse, p.FName_L2 as budgetItem,d.FValue as thisBudget " +
		" from "+a+" d inner join T_ORG_BaseUnit baseUnit on baseUnit.fid=d.FOrgUnitID "+ 
		" join T_MBG_BgItem I on I.FNumber=SUBSTRING(d.FFormula,CHARINDEX(':',d.FFormula)+1,(CHARINDEX(',',d.FFormula))-CHARINDEX(':',d.FFormula)-1) "+ 
		" join T_BD_Project p on p.FNumber=SUBSTRING(SUBSTRING(FFormula,CHARINDEX(',',FFormula)+1,LEN(FFormula)-LEN(substring(FFormula,0,charindex(',',FFormula)))), "+
		" CHARINDEX(':',SUBSTRING(FFormula,CHARINDEX(',',FFormula)+1,LEN(FFormula)-LEN(substring(FFormula,0,charindex(',',FFormula)))))+1 , "+
		" CHARINDEX(',',SUBSTRING(FFormula,CHARINDEX(',',FFormula)+1,LEN(FFormula)-LEN(substring(FFormula,0,charindex(',',FFormula)))))-CHARINDEX(':',SUBSTRING(FFormula,CHARINDEX(',',FFormula)+1,LEN(FFormula)-LEN(substring(FFormula,0,charindex(',',FFormula)))))-2) "+
		" inner join T_MBG_BgPeriod bgPeriod on bgPeriod.fid= d.FBgPeriodID where bgPeriod.fname_l2 = '"+da+"年' "+ffff;
    	
    	//Oracle 数据库
    	String sql="/*dialect*/  select baseUnit.FName_L2 as budgetTissue, I.FName_l2 as budgetCourse, p.FName_L2 as budgetItem,d.FValue as thisBudget " +
		" from "+a+" d inner join T_ORG_BaseUnit baseUnit on baseUnit.fid=d.FOrgUnitID "+
		" join T_MBG_BgItem I on I.FNumber=SUBSTR(d.FFormula,INSTR(d.FFormula,':')+1,(INSTR(d.FFormula,','))-INSTR(d.FFormula,':')-1) "+
		" join T_BD_Project p on p.FNumber=SUBSTR(SUBSTR(FFormula,INSTR(FFormula,',')+1,LENGTH(FFormula)-LENGTH(SUBSTR(FFormula,0,INSTR(FFormula,',')))), "+
        " INSTR(SUBSTR(FFormula,INSTR(FFormula,',')+1,LENGTH(FFormula)-LENGTH(SUBSTR(FFormula,0,INSTR(FFormula,',')))),':')+1 , "+ 
        " INSTR(SUBSTR(FFormula,INSTR(FFormula,',')+1,LENGTH(FFormula)-LENGTH(SUBSTR(FFormula,0,INSTR(FFormula,',')))),',')-INSTR(SUBSTR(FFormula,INSTR(FFormula,',')+1,LENGTH(FFormula)-LENGTH(SUBSTR(FFormula,0,INSTR(FFormula,',')))),':')-2) "+ 
		" inner join T_MBG_BgPeriod bgPeriod on bgPeriod.fid= d.FBgPeriodID where bgPeriod.fname_l2 = '"+da+"年' "+ffff;

    	IRowSet rs = null;
    	Boolean isOracle=true;   //是否oracle
    	try {   		
    		rs = SQLExecutorFactory.getRemoteInstance(sql).executeSQL();
		} catch (Exception e) {
			rs = SQLExecutorFactory.getRemoteInstance(sql_sqlServer).executeSQL();
			isOracle = false;
		}
    	
		Map<String,Map<String,String>> maps = new HashMap<String, Map<String,String>>();
		
    	//組織,科目,項目
		while (rs.next()) {
			
			Map<String, String> maps1=new HashMap<String, String>();
			String key =rs.getString("budgetItem");
		
				
			maps1.put("budgetTissue", rs.getString("budgetTissue"));
			maps1.put("budgetCourse", rs.getString("budgetCourse"));
    		
			maps1.put("budgetItem", rs.getString("budgetItem"));
    		
			maps1.put("thisBudget","0");
			maps1.put("thisBudget", rs.getString("thisBudget"));
 	
    		maps.put(key, maps1);
		}
    		
		
		 
		
		
		 //執行數		 		
//		 String b="/*dialect*/  select b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_TravelAccountBill b join T_BC_TravelAccountBillEntry e on e.FBillID=b.fid "+
//		 " join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_ORG_Admin a on a.fid=b.CFUseAdminOrgUnitI" +
//		 " left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID " +
//		 " where b.FState not in('20','25','27','30','40') and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff;
//		 
//		 
//		 String c="/*dialect*/ select b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_BizAccountBill b join T_BC_BizAccountBillEntry e on e.FBillID=b.fid "+
//		 " join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_ORG_Admin a on a.fid=b.CFUseAdminOrgUnitI" +
//		 " left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID " +
//		 " where b.FState not in('20','25','27','30','40') and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff;
//
//		 
//		 String d="/*dialect*/ select b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_DailyLoanBill b join T_BC_DailyLoanBillEntry e on e.FBillID=b.fid "+
//		 " join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=b.FCostedDeptId left join T_ORG_Admin a on a.fid=b.CFUseAdminOrgUnitI" +
//		 " left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID " +
//		 " where b.FState not in('20','25','27','30','40') and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff;
//
//		 
//		 String e="/*dialect*/ select b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_BizAccountOutBill b join T_BC_BizAccountOutBillEntry e on e.FBillID=b.fid "+
//		 " join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_ORG_Admin a on a.fid=b.CFUseAdminOrgUnitI" +
//		 " left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID " +
//		 " where b.FState not in('20','25','27','30','40') and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff;
//
//		 
//		 IRowSet rd1 = SQLExecutorFactory.getRemoteInstance(b).executeSQL();
//		 Map<String,Map<String, Map<String,String>>> map = new HashMap<String, Map<String,Map<String,String>>>();
//		 while (rd1.next()) {
//			 String key = rd1.getString("budgetItem");
//
//			 String key1=rd1.getString("number")+rd1.getString("useDepart")+rd1.getString("campus")+rd1.getString("department")+rd1.getString("applicant")+rd1.getString("reimDate");			 
////			 String key1=rd1.getString("useDepart")+rd1.getString("campus")+rd1.getString("department")+rd1.getString("applicant")+rd1.getString("reimDate");		
//			 		Map<String, Map<String, String>> map1=new HashMap<String, Map<String,String>>();
//		    		Map<String, String> map2=new HashMap<String, String>();		    		
////		    		if(){
//	    			    map2.put("useDepart","0");
//			    		map2.put("campus","0");
//			    		map2.put("department","0");
//			    		map2.put("money","0");
//			    		map2.put("applicant","0");
//			    		map2.put("reimDate","0");
//			    		
//			    		map2.put("budgetItem",rd1.getString("budgetItem"));
//			    		
//		    			 BigDecimal thisBudget=rd1.getBigDecimal("money");	
//			    		 BigDecimal bd = new BigDecimal(map2.get("money"));
//			    		 map2.put("money",bd.add(thisBudget).toString());
//			    		 map2.put("useDepart", rd1.getString("useDepart"));
//				    		map2.put("campus", rd1.getString("campus"));
//				    		map2.put("department", rd1.getString("department"));
//				    		map2.put("applicant", rd1.getString("applicant"));
//				    		map2.put("reimDate", rd1.getString("reimDate"));	
//				    		map1.put(key1, map2);
//				    		map.put(key, map1);
////		    		}
//
//		 }
//		 		 
//		 IRowSet rd2 = SQLExecutorFactory.getRemoteInstance(c).executeSQL();		
//		 while (rd2.next()) {
//			 String key =rd2.getString("budgetItem");
//			 String key1 = rd2.getString("number")+rd2.getString("useDepart")+rd2.getString("campus")+rd2.getString("department")+rd2.getString("applicant")+rd2.getString("reimDate");
//			 if(map.containsKey(key)){
//		    		Map<String, Map<String, String>> map1=map.get(key);
//		    		Map<String, String> map2=new HashMap<String, String>();		    		
//		    		     map2.put("money","0");
//
//		    		    	 map2.put("budgetItem",rd2.getString("budgetItem"));   
//			    		 BigDecimal thisBudget=rd2.getBigDecimal("money");	
//			    		 BigDecimal bd = new BigDecimal(map2.get("money"));
//			    		 map2.put("money",bd.add(thisBudget).toString());
//					
//			    		 map2.put("useDepart", rd2.getString("useDepart"));
//				    		map2.put("campus", rd2.getString("campus"));
//				    		map2.put("department", rd2.getString("department"));
//				    		map2.put("applicant", rd2.getString("applicant"));
//				    		map2.put("reimDate", rd2.getString("reimDate"));
//
//				    		map1.put(key1, map2);
//				    		map.put(key, map1);
//		    } 
//		 }
//		 
//	     IRowSet rd3 = SQLExecutorFactory.getRemoteInstance(d).executeSQL();		
//		 while (rd3.next()) {
//			 String key = rd3.getString("budgetItem");
//			 String key1 = rd3.getString("number")+rd3.getString("useDepart")+rd3.getString("campus")+rd3.getString("department")+rd3.getString("applicant")+rd3.getString("reimDate");
//			 if(map.containsKey(key)){
//		    		Map<String, Map<String, String>> map1=map.get(key);
//		    		Map<String, String> map2=new HashMap<String, String>();		    		
//	    		     map2.put("money","0");
//	    		
//	    				map2.put("budgetItem",rd3.getString("budgetItem"));
//		    	
//		    			 BigDecimal thisBudget=rd3.getBigDecimal("money");	
//			    		 BigDecimal bd = new BigDecimal(map2.get("money"));
//			    		 map2.put("money",bd.add(thisBudget).toString());
//			    		 map2.put("useDepart", rd3.getString("useDepart"));
//				    		map2.put("campus", rd3.getString("campus"));
//				    		map2.put("department", rd3.getString("department"));
//				    		map2.put("applicant", rd3.getString("applicant"));
//				    		map2.put("reimDate", rd3.getString("reimDate"));
//
//				    		map1.put(key1, map2);
//				    		map.put(key, map1);
//		    } 
//		 }
//		 
//		 
//		 IRowSet rd4 = SQLExecutorFactory.getRemoteInstance(e).executeSQL();		
//		 while (rd4.next()) {
//			 String key = rd4.getString("budgetItem");
//			 String key1 = rd4.getString("number")+rd4.getString("useDepart")+rd4.getString("campus")+rd4.getString("department")+rd4.getString("applicant")+rd4.getString("reimDate");
//			 if(map.containsKey(key)){
//		    		Map<String, Map<String, String>> map1=map.get(key);
//		    		Map<String, String> map2=new HashMap<String, String>();		    		
//	    		     map2.put("money","0");
//	    		
//	    				map2.put("budgetItem",rd4.getString("budgetItem"));
//	    		     
//		    			 BigDecimal thisBudget=rd4.getBigDecimal("money");	
//			    		 BigDecimal bd = new BigDecimal(map2.get("money"));
//			    		 map2.put("money",bd.add(thisBudget).toString());
//			    		 map2.put("useDepart", rd4.getString("useDepart"));
//				    		map2.put("campus", rd4.getString("campus"));
//				    		map2.put("department", rd4.getString("department"));
//				    		map2.put("applicant", rd4.getString("applicant"));
//				    		map2.put("reimDate", rd4.getString("reimDate"));
//
//				    		map1.put(key1, map2);
//				    		map.put(key, map1); 		
//		    } 
//		 }
		
		tblMain.removeRows(); 
		IRow row = null;
		 int z=0;
		 int y=0;
		 //SQL Server
		String sz_sqlServer = "/*dialect*/ select a.Cause as Cause,a.ItemNumber as ItemNumber,a.number as number,a.budgetTissue as budgetTissue,a.budgetItem as budgetItem,a.useDepart as useDepart,a.campus as campus,a.department as department,a.money as money,a.applicant as applicant,a.reimDate as reimDate from ( "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_TravelAccountBill b join T_BC_TravelAccountBillEntry e on e.FBillID=b.fid " + 
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff+
		" UNION "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_BizAccountBill b join T_BC_BizAccountBillEntry e on e.FBillID=b.fid "+ 
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff+
		" UNION "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_DailyLoanBill b join T_BC_DailyLoanBillEntry e on e.FBillID=b.fid "+ 
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=b.FCostedDeptId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff+
		" UNION "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as number,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,convert(varchar(100),b.FBizReqDate,23) as reimDate from T_BC_BizAccountOutBill b join T_BC_BizAccountOutBillEntry e on e.FBillID=b.fid "+ 
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and SUBSTRING(convert(varchar(20),b.FBizDate),6,5)= "+da+fff+
		" ) as a where convert(varchar(10),a.reimDate,120) >= '"+dat1+"' and convert(varchar(10),a.reimDate,120) <= '"+dat2+"'"+
		" order by  a.budgetItem,a.reimDate desc " ;
		
		//Oracle				
		String sz = "/*dialect*/ select a.Cause as Cause,a.ItemNumber as ItemNumber,a.fnumber as fnumber,a.budgetTissue as budgetTissue,a.budgetItem as budgetItem,a.useDepart as useDepart,a.campus as campus,a.department as department,a.money as money,a.applicant as applicant,a.reimDate as reimDate from ( "+ 
	    " select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as fnumber,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,to_char(b.FBizDate,'yyyy-mm-dd') as reimDate from T_BC_TravelAccountBill b join T_BC_TravelAccountBilLENtry e on e.FBillID=b.fid "+ 
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+ 
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"'  and to_char(b.FBizDate,'yyyy')= "+da+fff+
		" UNION "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as fnumber,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,to_char(b.FBizDate,'yyyy-mm-dd') as reimDate from T_BC_BizAccountBill b join T_BC_BizAccountBilLENtry e on e.FBillID=b.fid "+
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and to_char(b.FBizDate,'yyyy')= "+da+fff+
		" UNION "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as fnumber,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,to_char(b.FBizDate,'yyyy-mm-dd') as reimDate from T_BC_DailyLoanBill b join T_BC_DailyLoanBilLENtry e on e.FBillID=b.fid "+
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=b.FCostedDeptId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and to_char(b.FBizDate,'yyyy')= "+da+fff+
		" UNION "+
		" select b.FCause as Cause,p.fnumber as ItemNumber,b.FNumber as fnumber,c.FName_L2 as budgetTissue,p.FName_L2 as budgetItem,a.FName_L2 as useDepart,b.CFOnCampus as campus,c.FName_L2 as department, e.FAmountApproved as money,pe.FName_L2 as applicant,to_char(b.FBizDate,'yyyy-mm-dd') as reimDate from T_BC_BizAccountOutBill b join T_BC_BizAccountOutBilLENtry e on e.FBillID=b.fid "+ 
		" join T_BD_Project p on p.FID=e.FProjectId  join T_ORG_CostCenter c on c.fid=e.FCostCenterId left join T_BD_GeneralAsstActType a on a.fid=b.CFUseDepartmentID "+ 
		" left join T_ORG_Admin ad on ad.fid=b.FOrgUnitID left join T_BD_Person pe on pe.fid=b.FApplierID "+
		" where b.FState not in('20','25','27','30','40') and b.FControlUnitID = '"+companyId+"' and to_char(b.FBizDate,'yyyy')= "+da+fff+
		" )  a where a.reimDate >= '"+dat1+"' and a.reimDate <= '"+dat2+"'"+
		" order by  a.budgetItem,a.reimDate desc " ;
		
		  IRowSet ket = null;
		if(isOracle){
			ket = SQLExecutorFactory.getRemoteInstance(sz).executeSQL();	
		}else{
			ket = SQLExecutorFactory.getRemoteInstance(sz_sqlServer).executeSQL();	
		}
		 
		
		 while (ket.next()) {
			 
				 row = tblMain.addRow();
				 z++;
				 tblMain.setRowCount(z);
				 row.getCell("useDepart").setValue(ket.getString("useDepart"));					 						
				 row.getCell("campus").setValue(OnCampus.getEnum(ket.getString("campus")));					 
				 row.getCell("department").setValue(ket.getString("department"));
				 BigDecimal ba = new BigDecimal(ket.getString("money"));
//				 row.getCell("money").setValue(ba.setScale(2, BigDecimal.ROUND_HALF_UP));
				 DecimalFormat df4 = new DecimalFormat("#,##0.00");
				 row.getCell("money").setValue(df4.format(ba));
				 
				 row.getCell("applicant").setValue(ket.getString("applicant"));					 
				 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				 row.getCell("reimDate").setValue(sdf.parse(ket.getString("reimDate")));
				 
				 row.getCell("budgetItem").setValue(ket.getString("budgetItem"));
				 row.getCell("ItemNumber").setValue(ket.getString("ItemNumber"));
				 
				 row.getCell("cause").setValue(ket.getString("Cause"));
				 if(maps.containsKey(ket.getString("budgetItem"))){	
					 Map<String, String> maps1 = maps.get(ket.getString("budgetItem"));
					 row.getCell("budgetCourse").setValue(maps1.get("budgetCourse"));					 
					 BigDecimal bd = new BigDecimal(maps1.get("thisBudget"));
//					 row.getCell("budget").setValue(bd.setScale(2, BigDecimal.ROUND_HALF_UP));
					 row.getCell("budget").setValue(df4.format(bd));
				 }
			 
		 }
		 tblMain.setRowCount(z);	
			 
			 
			//填數
//			 IRow row = null;
//			 int z=0;
//			 int y=0;
//			 for(String key:maps.keySet()){      // maps预算
//				 Map<String, String> maps1 = maps.get(key);
//				 if(map.containsKey(key)){		 //	map执行	 
//					 Map<String, Map<String, String>> map1 = map.get(key);					 
//					 for(String key1:map1.keySet()){
//						 z++;
//						 row = tblMain.addRow();
//						 tblMain.setRowCount(z+y);
//						 Map<String, String> map2 = map1.get(key1);
//						 
//						 row.getCell("useDepart").setValue(map2.get("useDepart"));					 						
//						 row.getCell("campus").setValue(OnCampus.getEnum(map2.get("campus")));					 
//						 row.getCell("department").setValue(map2.get("department"));
//						 
//						 BigDecimal ba = new BigDecimal(map2.get("money"));
//						 row.getCell("money").setValue(ba.setScale(2, BigDecimal.ROUND_HALF_UP));
//						 
//						 row.getCell("applicant").setValue(map2.get("applicant"));					 
//						 SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
//						 row.getCell("reimDate").setValue(sdf.parse(map2.get("reimDate")));
//						 
//						 row.getCell("budgetItem").setValue(maps1.get("budgetItem"));
//						 row.getCell("budgetCourse").setValue(maps1.get("budgetCourse"));					 
//						 BigDecimal bd = new BigDecimal(maps1.get("thisBudget"));
//						 row.getCell("budget").setValue(bd.setScale(2, BigDecimal.ROUND_HALF_UP));
//					 }					 
//				 }else {   
////					 row = tblMain.addRow(); 
////					 y++;
////					 row.getCell("budgetItem").setValue(maps1.get("budgetItem"));
////					 row.getCell("budgetCourse").setValue(maps1.get("budgetCourse"));					 
////					 BigDecimal bd = new BigDecimal(maps1.get("thisBudget"));
////					 row.getCell("budget").setValue(bd.setScale(2, BigDecimal.ROUND_HALF_UP));					
//				}			 
//			 }

		   
			 

			 
			 
			 
			 tblMain.setRowCount(z+y);
    }
   	
	
	
  //刷新
    public void actionRefresh_actionPerformed(ActionEvent e) throws Exception
    {
    	super.actionRefresh_actionPerformed(e);
    	selectquery();
    	setValueToTable();
   	
    }

  //查询
    public void actionQuery_actionPerformed(ActionEvent e) throws Exception
    {
    	super.actionQuery_actionPerformed(e);
    	selectquery();
    	setValueToTable();
    	
    }
    
    
    /**
     * output class constructor
     */
    public BudgetDetailsRptUI() throws Exception
    {
        super();
    }

    /**
     * output storeFields method
     */
    public void storeFields()
    {
        super.storeFields();
    }

    /**
     * output tblMain_tableClicked method
     */
    protected void tblMain_tableClicked(com.kingdee.bos.ctrl.kdf.table.event.KDTMouseEvent e) throws Exception
    {
      //  super.tblMain_tableClicked(e);
    }

    /**
     * output menuItemImportData_actionPerformed method
     */
    protected void menuItemImportData_actionPerformed(java.awt.event.ActionEvent e) throws Exception
    {
        super.menuItemImportData_actionPerformed(e);
    }

    /**
     * output actionPageSetup_actionPerformed
     */
    public void actionPageSetup_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPageSetup_actionPerformed(e);
    }

    /**
     * output actionExitCurrent_actionPerformed
     */
    public void actionExitCurrent_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExitCurrent_actionPerformed(e);
    }

    /**
     * output actionHelp_actionPerformed
     */
    public void actionHelp_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionHelp_actionPerformed(e);
    }

    /**
     * output actionAbout_actionPerformed
     */
    public void actionAbout_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAbout_actionPerformed(e);
    }

    /**
     * output actionOnLoad_actionPerformed
     */
    public void actionOnLoad_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionOnLoad_actionPerformed(e);
    }

    /**
     * output actionSendMessage_actionPerformed
     */
    public void actionSendMessage_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionSendMessage_actionPerformed(e);
    }

    /**
     * output actionCalculator_actionPerformed
     */
    public void actionCalculator_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCalculator_actionPerformed(e);
    }

    /**
     * output actionExport_actionPerformed
     */
    public void actionExport_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExport_actionPerformed(e);
    }

    /**
     * output actionExportSelected_actionPerformed
     */
    public void actionExportSelected_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportSelected_actionPerformed(e);
    }

    /**
     * output actionRegProduct_actionPerformed
     */
    public void actionRegProduct_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionRegProduct_actionPerformed(e);
    }

    /**
     * output actionPersonalSite_actionPerformed
     */
    public void actionPersonalSite_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPersonalSite_actionPerformed(e);
    }

    /**
     * output actionProcductVal_actionPerformed
     */
    public void actionProcductVal_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionProcductVal_actionPerformed(e);
    }

    /**
     * output actionExportSave_actionPerformed
     */
    public void actionExportSave_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportSave_actionPerformed(e);
    }

    /**
     * output actionExportSelectedSave_actionPerformed
     */
    public void actionExportSelectedSave_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportSelectedSave_actionPerformed(e);
    }

    /**
     * output actionKnowStore_actionPerformed
     */
    public void actionKnowStore_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionKnowStore_actionPerformed(e);
    }

    /**
     * output actionAnswer_actionPerformed
     */
    public void actionAnswer_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAnswer_actionPerformed(e);
    }

    /**
     * output actionRemoteAssist_actionPerformed
     */
    public void actionRemoteAssist_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionRemoteAssist_actionPerformed(e);
    }

    /**
     * output actionPopupCopy_actionPerformed
     */
    public void actionPopupCopy_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPopupCopy_actionPerformed(e);
    }

    /**
     * output actionHTMLForMail_actionPerformed
     */
    public void actionHTMLForMail_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionHTMLForMail_actionPerformed(e);
    }

    /**
     * output actionExcelForMail_actionPerformed
     */
    public void actionExcelForMail_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExcelForMail_actionPerformed(e);
    }

    /**
     * output actionHTMLForRpt_actionPerformed
     */
    public void actionHTMLForRpt_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionHTMLForRpt_actionPerformed(e);
    }

    /**
     * output actionExcelForRpt_actionPerformed
     */
    public void actionExcelForRpt_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExcelForRpt_actionPerformed(e);
    }

    /**
     * output actionLinkForRpt_actionPerformed
     */
    public void actionLinkForRpt_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionLinkForRpt_actionPerformed(e);
    }

    /**
     * output actionPopupPaste_actionPerformed
     */
    public void actionPopupPaste_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPopupPaste_actionPerformed(e);
    }

    /**
     * output actionToolBarCustom_actionPerformed
     */
    public void actionToolBarCustom_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionToolBarCustom_actionPerformed(e);
    }

    /**
     * output actionCloudFeed_actionPerformed
     */
    public void actionCloudFeed_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCloudFeed_actionPerformed(e);
    }

    /**
     * output actionCloudShare_actionPerformed
     */
    public void actionCloudShare_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCloudShare_actionPerformed(e);
    }

    /**
     * output actionCloudScreen_actionPerformed
     */
    public void actionCloudScreen_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCloudScreen_actionPerformed(e);
    }

    /**
     * output actionXunTongFeed_actionPerformed
     */
    public void actionXunTongFeed_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionXunTongFeed_actionPerformed(e);
    }

    /**
     * output actionAddNew_actionPerformed
     */
    public void actionAddNew_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAddNew_actionPerformed(e);
    }

    /**
     * output actionView_actionPerformed
     */
    public void actionView_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionView_actionPerformed(e);
    }

    /**
     * output actionEdit_actionPerformed
     */
    public void actionEdit_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionEdit_actionPerformed(e);
    }

    /**
     * output actionRemove_actionPerformed
     */
    public void actionRemove_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionRemove_actionPerformed(e);
    }


    /**
     * output actionPrint_actionPerformed
     */
    public void actionPrint_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPrint_actionPerformed(e);
    }

    /**
     * output actionPrintPreview_actionPerformed
     */
    public void actionPrintPreview_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPrintPreview_actionPerformed(e);
    }

    /**
     * output actionLocate_actionPerformed
     */
    public void actionLocate_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionLocate_actionPerformed(e);
    }

  

    /**
     * output actionImportData_actionPerformed
     */
    public void actionImportData_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionImportData_actionPerformed(e);
    }

    /**
     * output actionAttachment_actionPerformed
     */
    public void actionAttachment_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionAttachment_actionPerformed(e);
    }

    /**
     * output actionExportData_actionPerformed
     */
    public void actionExportData_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionExportData_actionPerformed(e);
    }

    /**
     * output actionToExcel_actionPerformed
     */
    public void actionToExcel_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionToExcel_actionPerformed(e);
    }

    /**
     * output actionStartWorkFlow_actionPerformed
     */
    public void actionStartWorkFlow_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionStartWorkFlow_actionPerformed(e);
    }

    /**
     * output actionPublishReport_actionPerformed
     */
    public void actionPublishReport_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionPublishReport_actionPerformed(e);
    }

    /**
     * output actionCancel_actionPerformed
     */
    public void actionCancel_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCancel_actionPerformed(e);
    }

    /**
     * output actionCancelCancel_actionPerformed
     */
    public void actionCancelCancel_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionCancelCancel_actionPerformed(e);
    }

    /**
     * output actionQueryScheme_actionPerformed
     */
    public void actionQueryScheme_actionPerformed(ActionEvent e) throws Exception
    {
        super.actionQueryScheme_actionPerformed(e);
    }

	@Override
	protected ICoreBase getBizInterface() throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	protected String getEditUIName() {
		// TODO Auto-generated method stub
		return null;
	}

}